<?php
class Client_bill_model extends CI_MODEL
	{
		public function __construct()
			{
				parent::__construct();
				$this->load->database();
				
			}

		function populate_total_time($project_id)
			{
				$this->db->select('id');
				$query = $this->db->get_where('tasks',array('project_id' => $project_id));
				$task_id = $query->result_array();
				$k=0;
				$task = array();					
				foreach ($task_id as $row)
					{ 
						$task[$k] = $row['id'];
						$k++;
					}	
				for($i=0;$i<(count($task));$i++)
					{
						$this->db->select('time');
						$query = $this->db->get_where('invoice_info',array('task_id' => $task[$i]));
						$time_spent_per_task = $query->result_array();
						//$sql = $this->db->last_query();
						//echo($sql."<br/>");
						$k=0;
						$time = array();					
						foreach ($time_spent_per_task as $row)
							{ 
								$time[$k] = $row['time'];
								$k++;
							}	
						$tmp_ttl=0;
						for($j=0;$j<(count($time));$j++)
							{
								$tmp_time1 = preg_replace("/^([\d]{1,2})\:([\d]{2})$/", "00:$1:$2", $time[$j]);
								sscanf($tmp_time1, "%d:%d:%d", $hours, $minutes, $seconds);
								$tmp_time1 = $hours * 3600 + $minutes * 60 + $seconds;
								$tmp_ttl = $tmp_ttl + $tmp_time1;
							}	
						$seconds = $tmp_ttl;
						$hours = floor($seconds/3600);
						$remainder_1 = ($seconds % 3600);
						$minutes = floor($remainder_1 / 60);
						$seconds = ($remainder_1 % 60);
						if(strlen($hours) == 1) 
							{
								$hours = "0".$hours;
							}
						if(strlen($minutes) == 1) 
							{
								$minutes = "0".$minutes;
							}
						if(strlen($seconds) == 1) 
							{
								$seconds = "0".$seconds;
							}
						$display_time[$i] = $hours.":".$minutes.":".$seconds;	
					}
					//print_r($display_time);
				return 	$display_time;
			}
		
		function populate_client_bill($project_id)
			{	
				$this->db->select('id');
				$query = $this->db->get_where('tasks',array('project_id' => $project_id));
				$task_id = $query->result_array();
				$k=0;
				$task = array();					
				foreach ($task_id as $row)
					{ 
						$task[$k] = $row['id'];
						$k++;
					}	
				$display_time = array();
				for($i = 0;$i<(count($task));$i++)
					{
						$this->db->select('time');
						$query = $this->db->get_where('invoice_info',array('task_id' => $task[$i]));
						$time_spent_per_task = $query->result_array();
						$k=0;
						$time = array();					
						foreach ($time_spent_per_task as $row)
							{ 
								$time[$k] = $row['time'];
								$k++;
							}	
						$tmp_ttl=0;
						for($j=0;$j<count($time);$j++)
							{
								$tmp_time1 = preg_replace("/^([\d]{1,2})\:([\d]{2})$/", "00:$1:$2", $time[$j]);
								sscanf($tmp_time1, "%d:%d:%d", $hours, $minutes, $seconds);
								$tmp_time1 = $hours * 3600 + $minutes * 60 + $seconds;
								$tmp_ttl = $tmp_ttl + $tmp_time1;
							}
						$this->db->select('time_spent');
						$query = $this->db->get_where('tasks',array('project_id' => $project_id,'id' => $task[$i]));
						$tmp = $query->row_array();
						if(isset($tmp['time_spent']))
						$tmp = $tmp['time_spent'];
						else
						$tmp = "00:00:00";
						$tmp_time2 = preg_replace("/^([\d]{1,2})\:([\d]{2})$/", "00:$1:$2", $tmp);
						sscanf($tmp_time2, "%d:%d:%d", $hours, $minutes, $seconds);
						$tmp_time2 = $hours * 3600 + $minutes * 60 + $seconds;
						$time_remaining = $tmp_time2-$tmp_ttl; 
						$seconds = $time_remaining;
						$hours = floor($seconds/3600);
						$remainder_1 = ($seconds % 3600);
						$minutes = floor($remainder_1 / 60);
						$seconds = ($remainder_1 % 60);
							if(strlen($hours) == 1) 
								{
									$hours = "0".$hours;
								}
							if(strlen($minutes) == 1) 
								{
									$minutes = "0".$minutes;
								}
							if(strlen($seconds) == 1) 
								{
									$seconds = "0".$seconds;
								}
							$display_time[$i] = $hours.":".$minutes.":".$seconds;
					}
				return $display_time;
			}	
		
		function populate_client_invoice($client_id)
			{		
				$this->db->distinct('invoice_details.invoice_id');
				$this->db->select('invoice_details.*,invoice_info.project_id');
				$this->db->from('projects');
				$this->db->where('projects.client_id',$client_id);
				$this->db->join('invoice_info','invoice_info.project_id = projects.id');
				$this->db->join('invoice_details','invoice_details.invoice_id = invoice_info.invoice_id');				
				return  $this->db->get()->result_array();				
			}
		function populate_billing_cycle_by_project_id($project_id)
			{
				$this->db->distinct('invoice_id');
				$this->db->select('invoice_id');
				$this->db->from('invoice_info');
				$this->db->where('project_id',$project_id);
				$billing_cycle_by_project = $this->db->get()->result_array();
				$cycle_by_project_id = count($billing_cycle_by_project);
				return $cycle_by_project_id;
			}
			
		function populate_billing_cycle_by_client_id($client_id)
			{
				$cycle_by_client_id = array();
				$this->db->select('id');
				$this->db->from('projects');
				$this->db->where('client_id',$client_id);
				$project_id	= $this->db->get()->result_array();
				
				foreach($project_id as $project_id_row)
					{
						$this->db->distinct('invoice_id');
						$this->db->select('invoice_id');
						$this->db->from('invoice_info');
						$this->db->where('project_id',$project_id_row['id']);
						$billing_cycle_by_project = $this->db->get()->result_array();
						$cycle_by_client_id[] = count($billing_cycle_by_project);
					}
				return $cycle_by_client_id;
			}	
			
		function populate_project_id_by_client_id($client_id)	
			{
				$this->db->select('id,name');
				$this->db->from('projects');
				$this->db->where('client_id',$client_id);
				return $this->db->get()->result_array();
				//$sql = $this->db->last_query();
				//echo($sql."<br/>");
				//return $this;
				
			}
	}
?>